#!/usr/bin/env python3
"""
Summary翻译导入脚本 - 将翻译好的summary内容回填到数据库中
支持批量导入、错误检查和进度报告
"""

import sqlite3
import os
import logging
from typing import Dict, List, Tuple
import re

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

class TranslationImporter:
    """翻译导入器"""

    def __init__(self, db_path: str):
        self.db_path = db_path
        self.conn = None
        self.import_stats = {
            'metrics': {'total': 0, 'success': 0, 'failed': 0, 'skipped': 0},
            'variants': {'total': 0, 'success': 0, 'failed': 0, 'skipped': 0},
            'enum_values': {'total': 0, 'success': 0, 'failed': 0, 'skipped': 0}
        }

    def connect(self):
        """连接数据库"""
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.conn.execute("PRAGMA foreign_keys = ON")
            logger.info(f"成功连接数据库: {self.db_path}")
            return True
        except Exception as e:
            logger.error(f"连接数据库失败: {e}")
            return False

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            logger.info("数据库连接已关闭")

    def parse_translation_file(self, filename: str) -> Dict[int, str]:
        """解析翻译文件"""
        translations = {}

        try:
            if not os.path.exists(filename):
                logger.warning(f"翻译文件不存在: {filename}")
                return translations

            with open(filename, 'r', encoding='utf-8') as f:
                lines = f.readlines()

            for line_num, line in enumerate(lines, 1):
                line = line.strip()

                # 跳过空行和注释行
                if not line or line.startswith('#'):
                    continue

                # 解析 ID|Translation 格式
                if '|' not in line:
                    logger.warning(f"第{line_num}行格式错误，缺少分隔符: {line}")
                    continue

                parts = line.split('|', 1)  # 只分割第一个管道符
                if len(parts) != 2:
                    logger.warning(f"第{line_num}行格式错误: {line}")
                    continue

                try:
                    record_id = int(parts[0].strip())
                    translation = parts[1].strip()

                    # 还原转义的字符
                    translation = translation.replace('\\|', '|').replace('\\n', '\n')

                    if translation:  # 确保翻译内容不为空
                        translations[record_id] = translation
                    else:
                        logger.warning(f"第{line_num}行翻译内容为空，ID: {record_id}")

                except ValueError:
                    logger.warning(f"第{line_num}行ID格式错误: {parts[0]}")
                    continue

            logger.info(f"从 {filename} 解析了 {len(translations)} 条翻译记录")
            return translations

        except Exception as e:
            logger.error(f"解析翻译文件失败: {e}")
            return {}

    def import_metrics_translations(self, translations: Dict[int, str]):
        """导入Metrics表的翻译"""
        logger.info("开始导入Metrics表翻译...")

        if not translations:
            logger.info("没有Metrics翻译内容需要导入")
            return

        self.import_stats['metrics']['total'] = len(translations)

        try:
            cursor = self.conn.cursor()

            for record_id, translation in translations.items():
                try:
                    # 检查记录是否存在
                    cursor.execute("SELECT id FROM metrics WHERE id = ?", (record_id,))
                    if not cursor.fetchone():
                        logger.warning(f"Metrics表中不存在ID为 {record_id} 的记录")
                        self.import_stats['metrics']['failed'] += 1
                        continue

                    # 更新翻译
                    cursor.execute("""
                        UPDATE metrics
                        SET summary_zh = ?
                        WHERE id = ?
                    """, (translation, record_id))

                    self.import_stats['metrics']['success'] += 1

                except Exception as e:
                    logger.error(f"导入Metrics ID {record_id} 失败: {e}")
                    self.import_stats['metrics']['failed'] += 1

            # 提交更改
            self.conn.commit()
            logger.info(f"Metrics表翻译导入完成，成功: {self.import_stats['metrics']['success']}, 失败: {self.import_stats['metrics']['failed']}")

        except Exception as e:
            logger.error(f"导入Metrics翻译失败: {e}")

    def import_variants_translations(self, translations: Dict[int, str]):
        """导入Variants表的翻译"""
        logger.info("开始导入Variants表翻译...")

        if not translations:
            logger.info("没有Variants翻译内容需要导入")
            return

        self.import_stats['variants']['total'] = len(translations)

        try:
            cursor = self.conn.cursor()

            for record_id, translation in translations.items():
                try:
                    # 检查记录是否存在
                    cursor.execute("SELECT id FROM variants WHERE id = ?", (record_id,))
                    if not cursor.fetchone():
                        logger.warning(f"Variants表中不存在ID为 {record_id} 的记录")
                        self.import_stats['variants']['failed'] += 1
                        continue

                    # 更新翻译
                    cursor.execute("""
                        UPDATE variants
                        SET variant_summary_zh = ?
                        WHERE id = ?
                    """, (translation, record_id))

                    self.import_stats['variants']['success'] += 1

                except Exception as e:
                    logger.error(f"导入Variants ID {record_id} 失败: {e}")
                    self.import_stats['variants']['failed'] += 1

            # 提交更改
            self.conn.commit()
            logger.info(f"Variants表翻译导入完成，成功: {self.import_stats['variants']['success']}, 失败: {self.import_stats['variants']['failed']}")

        except Exception as e:
            logger.error(f"导入Variants翻译失败: {e}")

    def import_enum_values_translations(self, translations: Dict[int, str]):
        """导入Enum_values表的翻译"""
        logger.info("开始导入Enum_values表翻译...")

        if not translations:
            logger.info("没有Enum_values翻译内容需要导入")
            return

        self.import_stats['enum_values']['total'] = len(translations)

        try:
            cursor = self.conn.cursor()

            for record_id, translation in translations.items():
                try:
                    # 检查记录是否存在
                    cursor.execute("SELECT id FROM enum_values WHERE id = ?", (record_id,))
                    if not cursor.fetchone():
                        logger.warning(f"Enum_values表中不存在ID为 {record_id} 的记录")
                        self.import_stats['enum_values']['failed'] += 1
                        continue

                    # 更新翻译
                    cursor.execute("""
                        UPDATE enum_values
                        SET summary_zh = ?
                        WHERE id = ?
                    """, (translation, record_id))

                    self.import_stats['enum_values']['success'] += 1

                except Exception as e:
                    logger.error(f"导入Enum_values ID {record_id} 失败: {e}")
                    self.import_stats['enum_values']['failed'] += 1

            # 提交更改
            self.conn.commit()
            logger.info(f"Enum_values表翻译导入完成，成功: {self.import_stats['enum_values']['success']}, 失败: {self.import_stats['enum_values']['failed']}")

        except Exception as e:
            logger.error(f"导入Enum_values翻译失败: {e}")

    def validate_import(self):
        """验证导入结果"""
        logger.info("验证导入结果...")

        try:
            cursor = self.conn.cursor()

            # 验证Metrics表
            cursor.execute("""
                SELECT
                    COUNT(*) as total,
                    COUNT(summary_zh) as translated,
                    COUNT(CASE WHEN summary_zh IS NOT NULL AND summary_zh != '' THEN 1 END) as non_empty_translated
                FROM metrics
                WHERE summary IS NOT NULL AND summary != ''
            """)
            metrics_stats = cursor.fetchone()
            logger.info(f"Metrics表验证: 总计={metrics_stats[0]}, 已翻译={metrics_stats[1]}, 非空翻译={metrics_stats[2]}")

            # 验证Variants表
            cursor.execute("""
                SELECT
                    COUNT(*) as total,
                    COUNT(variant_summary_zh) as translated,
                    COUNT(CASE WHEN variant_summary_zh IS NOT NULL AND variant_summary_zh != '' THEN 1 END) as non_empty_translated
                FROM variants
                WHERE variant_summary IS NOT NULL AND variant_summary != ''
            """)
            variants_stats = cursor.fetchone()
            logger.info(f"Variants表验证: 总计={variants_stats[0]}, 已翻译={variants_stats[1]}, 非空翻译={variants_stats[2]}")

            # 验证Enum_values表
            cursor.execute("""
                SELECT
                    COUNT(*) as total,
                    COUNT(summary_zh) as translated,
                    COUNT(CASE WHEN summary_zh IS NOT NULL AND summary_zh != '' THEN 1 END) as non_empty_translated
                FROM enum_values
                WHERE summary IS NOT NULL AND summary != ''
            """)
            enum_stats = cursor.fetchone()
            logger.info(f"Enum_values表验证: 总计={enum_stats[0]}, 已翻译={enum_stats[1]}, 非空翻译={enum_stats[2]}")

        except Exception as e:
            logger.error(f"验证导入结果失败: {e}")

    def generate_import_report(self):
        """生成导入报告"""
        try:
            with open('import_report.txt', 'w', encoding='utf-8') as f:
                f.write("# Summary翻译导入报告\n\n")

                total_success = 0
                total_failed = 0
                total_total = 0

                for table, stats in self.import_stats.items():
                    f.write(f"## {table.upper()}表\n")
                    f.write(f"- 总数: {stats['total']}\n")
                    f.write(f"- 成功: {stats['success']}\n")
                    f.write(f"- 失败: {stats['failed']}\n")
                    f.write(f"- 跳过: {stats['skipped']}\n")

                    if stats['total'] > 0:
                        success_rate = (stats['success'] / stats['total']) * 100
                        f.write(f"- 成功率: {success_rate:.2f}%\n")

                    f.write("\n")

                    total_success += stats['success']
                    total_failed += stats['failed']
                    total_total += stats['total']

                f.write("## 总体统计\n")
                f.write(f"- 总数: {total_total}\n")
                f.write(f"- 总成功: {total_success}\n")
                f.write(f"- 总失败: {total_failed}\n")

                if total_total > 0:
                    overall_success_rate = (total_success / total_total) * 100
                    f.write(f"- 总成功率: {overall_success_rate:.2f}%\n")

            logger.info("导入报告已保存到 import_report.txt")

        except Exception as e:
            logger.error(f"生成导入报告失败: {e}")

    def import_all_translations(self):
        """导入所有翻译文件"""
        logger.info("开始导入所有翻译文件...")

        # 检查翻译文件是否存在
        translation_files = [
            ('metrics_summaries_zh.txt', 'metrics'),
            ('variants_summaries_zh.txt', 'variants'),
            ('enum_summaries_zh.txt', 'enum_values')
        ]

        for filename, table_name in translation_files:
            if not os.path.exists(filename):
                logger.warning(f"翻译文件不存在，跳过: {filename}")
                self.import_stats[table_name]['skipped'] = 1
                continue

            logger.info(f"正在处理翻译文件: {filename}")
            translations = self.parse_translation_file(filename)

            if table_name == 'metrics':
                self.import_metrics_translations(translations)
            elif table_name == 'variants':
                self.import_variants_translations(translations)
            elif table_name == 'enum_values':
                self.import_enum_values_translations(translations)

        # 验证导入结果
        self.validate_import()

        # 生成导入报告
        self.generate_import_report()

        logger.info("所有翻译文件导入完成！")

def main():
    """主函数"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 从 extractor/translate -> extractor -> project root -> backend/data
    project_root = os.path.dirname(os.path.dirname(current_dir))
    db_path = os.path.join(project_root, 'backend', 'data', 'uma_insight.db')

    if not os.path.exists(db_path):
        logger.error(f"数据库文件不存在: {db_path}")
        return

    importer = TranslationImporter(db_path)

    if not importer.connect():
        return

    try:
        importer.import_all_translations()

    except KeyboardInterrupt:
        logger.info("用户中断导入过程")
    except Exception as e:
        logger.error(f"导入过程出错: {e}")
    finally:
        importer.close()

if __name__ == "__main__":
    main()